<?php

      // header("Content-Type: application/json");
       
        
   
       $tipo=$_GET['tipo'];
       $initipo=$_GET['ini'];
       $fitipo=$_GET['finn'];
      // $initipo=1962;
       //$fitipo=2014;
       $sort=$_GET['sort'];
       //$sort='yes';
       $fd=  fopen("archivos/datostipo.txt", "w");
        //Se almacena la conexion en la variable $con
        //$tipo="individual";

        $con=mysqli_connect("192.168.1.10","srojas","anemona") or die("Failed to connect with database!!!!");
        //mysqli_select_db( $con,"hermes2"); 
        mysqli_select_db($con,"ifcweb_investigadores");
      
        
        if($tipo==="departamento"){
        // The Chart table contains two fields: publicacion_year and publicaciones
        
      
        $sth = mysqli_query($con,"select d.nombre as departamento,count(*) as publicaciones from hermes2.papers_vs_citas as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id"
                . " join ifcweb_investigadores.departamentos as d on p.id_depto=d.id where publicacion_year>=$initipo and publicacion_year<=$fitipo and pc.doi is not null group by d.nombre;");
        
        
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'departamento', 'type' => 'string'),
        array('label' => 'publicaciones', 'type' => 'number')

        );
        
        fwrite($fd, "Departamento   num_publicaciones"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['departamento']."\t");
            
            fwrite($fd, $r['publicaciones']."\r\n");
            
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['departamento'])); 
        
        // Values of each slice
        $temp[] = array('v' => (int) $r['publicaciones']); 
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        
        //echo json_encode($table);
        fclose($fd);
        
        }
        elseif ($tipo==="division") {
          
        $sth = mysqli_query($con,"select dv.nombre as division ,count(*) as publicaciones from hermes2.papers_vs_citas as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id "
        . "join departamentos as d on p.id_depto=d.id join divisiones as dv on d.id_division=dv.id where publicacion_year>=$initipo and publicacion_year<=$fitipo and pc.doi is not null group by dv.nombre");
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'division', 'type' => 'string'),
        array('label' => 'Publicaciones', 'type' => 'number')

        );
        
        fwrite($fd, "Division   num_publicaciones"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['division']."\t");
            fwrite($fd, $r['publicaciones']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['division'])); 

        // Values of each slice
        $temp[] = array('v' => (int) $r['publicaciones']); 
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        
        //echo json_encode($table);
        
        fclose($fd);
        
        }
        elseif ($tipo==="investigador"){
            
            if ($sort==="yes"){
                $sth=mysqli_query($con,"select p.nombre as investigador, count(*) as publicaciones from hermes2.papers_vs_citas
                        as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id
                        where publicacion_year>=$initipo and publicacion_year<=$fitipo and pc.doi is not null group by p.nombre order by publicaciones asc;");
            }elseif($sort==="no"){
           
                $sth = mysqli_query($con,"select p.nombre as investigador, count(*) as publicaciones from  hermes2.papers_vs_citas
                        as pc join ifcweb_investigadores.perfiles as p on pc.autor_id=p.id
                        where publicacion_year>=$initipo and publicacion_year<=$fitipo and pc.doi is not null group by p.nombre;");
            }
        $rows = array();
        $table = array();
        $table['cols'] = array(

        // Labels for your chart, these represent the column titles
        // Note that one column is in "string" format and another one is in "number" format as pie chart only required "numbers" for calculating percentage and string will be used for column title
        array('label' => 'nombre', 'type' => 'string'),
        array('label' => 'Publicaciones', 'type' => 'number')

        );
        
        fwrite($fd, "Investigador   num_publicaciones"."\r\n");
        while($r = mysqli_fetch_assoc($sth)) {
            fwrite($fd,$r['investigador']."\t");
            fwrite($fd, $r['publicaciones']."\r\n");
        $temp = array();
        // the following line will be used to slice the Pie chart
        $temp[] = array('v' => (string)  utf8_encode( $r['investigador'])); 

        // Values of each slice
        $temp[] = array('v' => (int) $r['publicaciones']); 
        $rows[] = array('c' => $temp);
        }

        $table['rows'] = $rows;
        fclose($fd);
        //echo json_encode($table);
        }
        //}
        

        mysqli_close($con);
       
        
        //$jsonTable = json_encode($table);
        echo json_encode($table);
        //echo ''.json_encode($jsonTable).'';
        //echo $jsonTable;
        //print_r($jsonTable)
        
        
        //$arr = array('a' => 1, 'b' => 2, 'c' => 3, 'd' => 4, 'e' => 5);

        //
        //echo json_encode($arr);
        
        
        ?>

